﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.UI.WebControls;

/// <summary>
/// Summary description for Methods
/// </summary>
public class Methods
{
	public Methods()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    static string connStr = ConfigurationManager.ConnectionStrings["CSConn"].ToString();
    static SqlConnection conn = new SqlConnection(connStr);

    public static DateTime getBirthDate(String d, String m, String y)
    {
        Int32 day = Int32.Parse(d);
        Int32 month = Int32.Parse(m);
        Int32 year = Int32.Parse(y);
        DateTime dt = new DateTime(year, month, day);
        return dt;
    }

    public static bool conditionsRadio(RadioButtonList r)
    {
        bool b;
        if (r.SelectedValue == "Yes")
            b = true;
        else
            b = false;

        return b;
    }

    public static bool conditionsCheckitem(CheckBoxList c, int i)
    {
        bool b;
        if (c.Items[i].Selected)
            b = true;
        else
            b = false;

        return b;
    }

    public static bool conditionsCheck(CheckBox c)
    {
        bool b;
        if (c.Checked == true)
            b = true;
        else
            b = false;

        return b;
    }

    public static void AddEducation(int b, TextBox t, TextBox d1, TextBox d2, EventArgs e)
    {
        SqlCommand cmd1 = new SqlCommand("INSERT INTO SchoolsUniversities(SUName) VALUES(@name) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@name", t.Text));

        conn.Open();
        int suID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO BSEducationalBackground(BSID, SUID, YearsCompleted, GraduationYear) " +
            "VALUES (@bsID, @suID, @years_completed, @grad_year)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.Add(new SqlParameter("@bsID", b));
        cmd2.Parameters.Add(new SqlParameter("@suID", suID));
        cmd2.Parameters.Add(new SqlParameter("@years_completed", Convert.ToInt32(d1.Text)));
        cmd2.Parameters.Add(new SqlParameter("@grad_year", Convert.ToInt32(d2.Text)));

        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

    public static void AddEmployment(int b, TextBox t1, TextBox t2, TextBox t3, EventArgs e)
    {
        SqlCommand cmd1 = new SqlCommand("INSERT INTO Employers(EName) VALUES(@e_name) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@e_name", t1.Text));

        conn.Open();
        int empID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO BSEmploymentBackground(BSID, EDate, Duties, EmployerID) " +
        "VALUES (@bsID, @date, @duties, @empID)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.Add(new SqlParameter("@bsID", b));
        cmd2.Parameters.Add(new SqlParameter("@empID", empID));
        cmd2.Parameters.Add(new SqlParameter("@date", t2.Text));
        cmd2.Parameters.Add(new SqlParameter("@duties", t3.Text));

        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

    public static void AddHobby(int b, TextBox t)
    {
        SqlCommand cmd1 = new SqlCommand("INSERT INTO HobbiesSkills(HSName) VALUES(@h_name) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@h_name", t.Text));

        conn.Open();
        int hsID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO HSs_BSs(BSID, HSID) VALUES (@bsID, @hsID)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.Add(new SqlParameter("@bsID", b));
        cmd2.Parameters.Add(new SqlParameter("@hsID", hsID));

        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

    public static void AddEmergencyContact(int a, TextBox t1, TextBox t2, TextBox t3, TextBox t4)
    {
        SqlCommand cmd3 = new SqlCommand("INSERT INTO EmergencyContacts (ECName, MobileNo, PhoneNo) " +
            "VALUES (@ec_name, @ec_mobile_num, @ec_phone_num) SELECT SCOPE_IDENTITY()", conn);
        cmd3.CommandType = CommandType.Text;

        cmd3.Parameters.Add(new SqlParameter("@ec_name", t1.Text));
        cmd3.Parameters.Add(new SqlParameter("@ec_mobile_num", t2.Text));
        cmd3.Parameters.Add(new SqlParameter("@ec_phone_num", t3.Text));

        conn.Open();
        int ecID = Convert.ToInt32((decimal)cmd3.ExecuteScalar());
        conn.Close();

        SqlCommand cmd5 = new SqlCommand("INSERT INTO EmergencyContacts_Accounts (AccountID, ECID, Relationship) " +
            "VALUES (@acctID, @ecID, @relationship)", conn);
        cmd5.CommandType = CommandType.Text;

        cmd5.Parameters.Add(new SqlParameter("@acctID", a));
        cmd5.Parameters.Add(new SqlParameter("@ecID", ecID));
        cmd5.Parameters.Add(new SqlParameter("@relationship", t4.Text));

        conn.Open();
        cmd5.ExecuteNonQuery();
        conn.Close();
    }

    public static void AddScheduleSlot(int bsID, int dhID, string day)
    {
        int bssID = 0;
        SqlCommand cmd1 = new SqlCommand("INSERT INTO BSSchedule(BSID, S_Day) VALUES (@bsID, @day) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@bsID", bsID));
        cmd1.Parameters.Add(new SqlParameter("@day", day));

        conn.Open();
        bssID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO DH_BSS(BSSID, DHID) VALUES(@bssID, @dhID)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.Add(new SqlParameter("@bssID", bssID));
        cmd2.Parameters.Add(new SqlParameter("@dhID", dhID));

        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

    public static void deleteLanguages(int b)
    {
        string delLang = "DELETE FROM BabySitters_Languages WHERE BSID = @bsid ";
        
        SqlCommand cmd = new SqlCommand(delLang, conn);
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.Add(new SqlParameter("@bsid", b));

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }

    public static void deleteEducation(int b)
    {
        SqlCommand count = new SqlCommand("SELECT COUNT (SUID) FROM BSEducationalBackground WHERE BSID = @bsid", conn);
        count.CommandType = CommandType.Text;

        count.Parameters.Add(new SqlParameter("@bsid", b));

        conn.Open();
        int x = (int)count.ExecuteScalar();
        conn.Close();

        for (int i = 0; i < x; i++)
        {
            SqlCommand cmd = new SqlCommand("UPDATE SchoolsUniversities SET Deleted = 'true' WHERE SUID = (SELECT TOP 1 SUID FROM BSEducationalBackground WHERE BSID = @bsid) " +
                "DELETE TOP (1) FROM BSEducationalBackground WHERE BSID = @bsid DELETE TOP (1) FROM SchoolsUniversities WHERE Deleted = 'true' ", conn);
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add(new SqlParameter("@bsid", b));

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }     
    }

    public static void deleteEmployment(int b)
    {
        SqlCommand count = new SqlCommand("SELECT COUNT (EmployerID) FROM BSEmploymentBackground WHERE BSID = @bsid", conn);
        count.CommandType = CommandType.Text;

        count.Parameters.Add(new SqlParameter("@bsid", b));

        conn.Open();
        int x = (int)count.ExecuteScalar();
        conn.Close();

        for (int i = 0; i < x; i++)
        {
            SqlCommand cmd = new SqlCommand("UPDATE Employers SET Deleted = 'true' WHERE EmployerID = (SELECT TOP (1) EmployerID FROM BSEmploymentBackground WHERE BSID = @bsid) " +
            "DELETE TOP (1) FROM BSEmploymentBackground WHERE BSID = @bsid DELETE TOP (1) FROM Employers WHERE Deleted = 'true' ", conn);
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add(new SqlParameter("@bsid", b));

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }     
        
    }

    public static void deleteHobbies(int b)
    {
        SqlCommand count = new SqlCommand("SELECT COUNT (HSID) FROM HSs_BSs WHERE BSID = @bsid", conn);
        count.CommandType = CommandType.Text;

        count.Parameters.Add(new SqlParameter("@bsid", b));

        conn.Open();
        int x = (int)count.ExecuteScalar();
        conn.Close();

        for (int i = 0; i < x; i++)
        {
            SqlCommand cmd = new SqlCommand("UPDATE HobbiesSkills SET Deleted = 'true' WHERE HSID = (SELECT TOP (1) HSID FROM HSs_BSs WHERE BSID = @bsid) " +
            "DELETE TOP (1) FROM HSs_BSs WHERE BSID = @bsid DELETE TOP (1) FROM HobbiesSkills WHERE Deleted = 'true' ", conn);
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add(new SqlParameter("@bsid", b));

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }    
    }

    public static void deleteSchedule(int b)
    {
        SqlCommand count = new SqlCommand("SELECT COUNT (BSSID) FROM BSSchedule WHERE BSID = @bsid", conn);
        count.CommandType = CommandType.Text;

        count.Parameters.Add(new SqlParameter("@bsid", b));

        conn.Open();
        int x = (int)count.ExecuteScalar();
        conn.Close();

        for (int i = 0; i < x; i++)
        {
            SqlCommand cmd = new SqlCommand("DELETE FROM DH_BSS WHERE BSSID = (SELECT TOP 1 BSSID FROM BSSchedule WHERE BSID = @bsid) DELETE TOP (1) FROM BSSchedule WHERE BSID = @bsid ", conn);
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add(new SqlParameter("@bsid", b));

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }    
    }

    public static void deleteEmergencyContacts(int b)
    {
        SqlCommand count = new SqlCommand("SELECT COUNT (ECID) FROM EmergencyContacts_Accounts WHERE AccountID = (SELECT AccountID FROM BabySitters WHERE BSID = @bsid)", conn);
        count.CommandType = CommandType.Text;

        count.Parameters.Add(new SqlParameter("@bsid", b));

        conn.Open();
        int x = (int)count.ExecuteScalar();
        conn.Close();

        for (int i = 0; i < x; i++)
        {
            SqlCommand cmd = new SqlCommand("UPDATE EmergencyContacts SET Deleted = 'true' WHERE ECID = (SELECT TOP 1 ECID FROM EmergencyContacts_Accounts WHERE AccountID = (SELECT AccountID FROM BabySitters WHERE BSID = @bsid)) " +
            "DELETE TOP (1) FROM EmergencyContacts_Accounts WHERE AccountID = (SELECT AccountID FROM BabySitters WHERE BSID = @bsid) DELETE FROM EmergencyContacts WHERE Deleted = 'true' ", conn);
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add(new SqlParameter("@bsid", b));

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        } 
    }

    public static void deleteExisting(int b)
    {
        deleteEducation(b);
        deleteEmergencyContacts(b);
        deleteEmployment(b);
        deleteHobbies(b);
        deleteLanguages(b);
        deleteSchedule(b);
    }
}